﻿CREATE PROCEDURE [ServiceEngine].[GetOperationVersionsForEndpointAndAction]
	@EndpointID uniqueidentifier,
	@OperationName nvarchar(4000),
	@VersionMajor int,
	@VersionMinor int,
	@VersionBuild int,
	@VersionQFE int
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @OperationID uniqueidentifier

	IF @OperationName is null
	BEGIN
		SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
				OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, R.Moniker
				,OV.OperationID, OV.ResourceID, OV.PolicyID
		FROM	[ServiceEngine].OperationVersions OV		
		JOIN	[ServiceEngine].Endpoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
		LEFT OUTER JOIN	[ServiceEngine].Resources R on R.ResourceID = OV.ResourceID
		WHERE	AO.EndpointID = @EndpointID
		AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
		ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
	END
	ELSE
	BEGIN
		SELECT  @OperationID = N.EntityID
		FROM	[ServiceEngine].Names N
		WHERE	N.EntityTypeID = 4
		AND		N.EntityName = @OperationName

		IF @VersionMajor is null
		BEGIN
			SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
					OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, R.Moniker
					,OV.OperationID, OV.ResourceID, OV.PolicyID
			FROM	[ServiceEngine].OperationVersions OV
			JOIN	[ServiceEngine].Endpoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
			LEFT OUTER JOIN		[ServiceEngine].Resources R on R.ResourceID = OV.ResourceID			
			WHERE	AO.EndpointID = @EndpointID
			AND		OV.OperationID = @OperationID
			AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
			ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
		END
		ELSE
		BEGIN
			IF @VersionMinor is null
			BEGIN
				SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
						OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, R.Moniker
						,OV.OperationID, OV.ResourceID, OV.PolicyID
				FROM	[ServiceEngine].OperationVersions OV
				JOIN	[ServiceEngine].Endpoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
				LEFT OUTER JOIN		[ServiceEngine].Resources R on R.ResourceID = OV.ResourceID				
				WHERE	AO.EndpointID = @EndpointID
				AND		OV.OperationID = @OperationID
				AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
				AND		OV.VersionMajor = @VersionMajor
				ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
			END
			ELSE
			BEGIN
				IF @VersionBuild is null
				BEGIN
					SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
							OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, R.Moniker
							,OV.OperationID, OV.ResourceID, OV.PolicyID
					FROM	[ServiceEngine].OperationVersions OV
					JOIN	[ServiceEngine].Endpoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
					LEFT OUTER JOIN		[ServiceEngine].Resources R on R.ResourceID = OV.ResourceID					
					WHERE	AO.EndpointID = @EndpointID
					AND		OV.OperationID = @OperationID
					AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
					AND		OV.VersionMajor = @VersionMajor
					AND		OV.VersionMinor = @VersionMinor
					ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
				END
				ELSE
				BEGIN
					IF @VersionQFE is null
					BEGIN
						SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
								OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, R.Moniker
								,OV.OperationID, OV.ResourceID, OV.PolicyID
						FROM	[ServiceEngine].OperationVersions OV
						JOIN	[ServiceEngine].Endpoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
						LEFT OUTER JOIN		[ServiceEngine].Resources R on R.ResourceID = OV.ResourceID						
						WHERE	AO.EndpointID = @EndpointID
						AND		OV.OperationID = @OperationID
						AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
						AND		OV.VersionMajor = @VersionMajor
						AND		OV.VersionMinor = @VersionMinor
						AND		OV.VersionBuild = @VersionBuild
						ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
					END
					ELSE
					BEGIN
						SELECT	OV.OperationVersionID as ID, OV.VersionMajor, OV.VersionMinor, OV.VersionBuild, OV.VersionQFE,
								OV.AutoResponse, OV.IsTestable, OV.IsActive, OV.RequestTransformXSLT, OV.ResponseTransformXSLT, R.Moniker
								,OV.OperationID, OV.ResourceID, OV.PolicyID
						FROM	[ServiceEngine].OperationVersions OV
						JOIN	[ServiceEngine].Endpoints_AssociatedOperations AO on AO.OperationVersionID = OV.OperationVersionID
						LEFT OUTER JOIN		[ServiceEngine].Resources R on R.ResourceID = OV.ResourceID						
						WHERE	AO.EndpointID = @EndpointID
						AND		OV.OperationID = @OperationID
						AND		(OV.IsActive = 1 OR OV.IsTestable = 1)
						AND		OV.VersionMajor = @VersionMajor
						AND		OV.VersionMinor = @VersionMinor
						AND		OV.VersionBuild = @VersionBuild
						AND		OV.VersionQFE = @VersionQFE
						ORDER BY OV.OperationID, OV.VersionMajor DESC, OV.VersionMinor DESC, OV.VersionBuild DESC, OV.VersionQFE DESC
					END
				END
			END
		END
	END
END


